package dto;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import vo.attendenceVO;
import vo.employeesVO;
import db.MySql;

public class attendenceDTO {

	
	private employeesVO evo = null;
	private attendenceVO avo = null;
	
	private Connection conn = null;
	
	public attendenceDTO() {
		conn = new MySql().getConn();
	}

	public String attendence_state(employeesVO evo){
		String state = "미출근";
		
		String sql = "select date_format(s_time,'%H%i') as time_s, "
				+ " date_format(e_time,'%H%i') as time_e "
				+ " from semyadmin.attendence "
				+ " where e_number = ? and "
				+ " substr(s_time,1,10) = curdate() ";
		
		try {
			
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setLong(1, evo.getE_number());
			
			ResultSet rs = pst.executeQuery();
			
			while (rs.next()) {
				System.out.println("time_s" + Integer.parseInt(rs.getString("time_s")));
				System.out.println("time_e" + Integer.parseInt(rs.getString("time_e")));
				if(Integer.parseInt(rs.getString("time_s")) > Integer.parseInt(rs.getString("time_e"))){
					state = "업무중";
				}
				else{
					state = "퇴근";
				}
				System.out.println("state" + state);
				rs.close();
				pst.close();
				conn.close();
			}
		} catch (Exception e) {
		}
		return state;
	}
	public String sTime(employeesVO evo){
		conn = new MySql().getConn();
		this.evo = evo;
		String s_time = "확인 필요";
		String sql = "select date_format(s_time,'%Y년%m월%d일 %p %h:%i') as time "
				+ " from semyadmin.attendence "
				+ " where e_number = ? and "
				+ " substr(s_time,1,10) = curdate() ";
		
		try {
			
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setLong(1, evo.getE_number());

			ResultSet rs = pst.executeQuery();

			while (rs.next()) {
				s_time = rs.getString("time");
			}
			
			rs.close();
			pst.close();
			conn.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return s_time;
	}
	
	public void check_in(employeesVO evo){
		this.evo = evo;
		
		String sql = " insert into semyadmin.attendence  "
				+ " value( concat(DATE_FORMAT(now(), '%Y%m%d'),?), "
				+ " now(), curdate(), ?) ";
		
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			
			pst = conn.prepareStatement(sql);
			pst.setLong(1, evo.getE_number() );
			pst.setLong(2, evo.getE_number());
			
			int resultValue = pst.executeUpdate();
			
			pst.close();
			conn.close();
		} catch (Exception e) {
			e.getStackTrace();
		}
	}
	
	public void check_out(employeesVO evo){
		this.evo = evo;
		
		String sql = " update semyadmin.attendence set "
				+ " e_time = now() "
				+ " where e_number = ? ";
		
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			
			pst = conn.prepareStatement(sql);
			pst.setLong(1, evo.getE_number());
			
			int resultValue = pst.executeUpdate();
			
			pst.close();
			conn.close();
		} catch (Exception e) {
			e.getStackTrace();
		}
	}
	
}
